--This script should be executed in the database having the SSIS Configurations table used by the
--package configuration value defined in the @Configuration variable below.


set nocount on;


--declare variables
declare @Configuration as varchar(100)
declare @ETLLogDbServer as varchar(35)
declare @AgentJobsServer as varchar(35)
declare @ETLLogDb as varchar(75)


--set variables
set @Configuration = 'ETL Environment'
set @ETLLogDbServer = 'waBIETL1'
set @AgentJobsServer = 'waBISQL1'
set @ETLLogDb = 'BIMonitor'


--xact_abort being on makes the entire transaction (below) behave as a single unit.  All SQL within the
--transaction will succeed (if no errors occur) or fail (if an error occurs) as a unit.
set xact_abort on


--wrap DML SQL within a transaction
begin tran


--remove all old configuration settings
delete
from
  BIMonitor.SSISConfigurations
where
  ConfigurationFilter = @Configuration


--add new configuration settings
insert into BIMonitor.SSISConfigurations
 (ConfigurationFilter,ConfiguredValue,PackagePath,ConfiguredValueType)
values
 (@Configuration,'Data Source=' + @ETLLogDbServer + ';Initial Catalog=' + @ETLLogDb + 
  ';Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;',
  '\Package.Connections[ETLLogging OLE_SQL].Properties[ConnectionString]','String')


commit tran


--turn xact_abort off
set xact_abort off